package it.univaq.disim.mwt.business.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import it.univaq.disim.mwt.business.VotiService;
import it.univaq.disim.mwt.business.model.Alunno;
import it.univaq.disim.mwt.business.model.Materia;
import it.univaq.disim.mwt.business.model.RegistroPersonale;
import it.univaq.disim.mwt.business.model.TipologiaSegreteria;
import it.univaq.disim.mwt.business.model.Voto;

public class JDBCVotoService implements VotiService{

	Connection connection = null;
	PreparedStatement preparedStatement = null;
	ResultSet resultSet = null;
	Voto voto=null;
	
	@Override
	public Voto findAllVotiById(Long id) {
		// TODO Auto-generated method stub
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Voto voto=null;
		String sql=" select * from voti where id = ? ";
			
        try {
        	connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "school", "school");
    		preparedStatement = connection.prepareStatement(sql);
    		preparedStatement.setLong(1, id);
    		preparedStatement.executeQuery();
			resultSet = preparedStatement.getResultSet();
			resultSet.next();
			
			Long ide=resultSet.getLong("id");
			Long idAlunno=resultSet.getLong("id_alunno");
			float valutazione=resultSet.getFloat("valutazione");
			Date data= resultSet.getDate("data");
			Long idRegistroPersonale=resultSet.getLong("id_registro_personlae");
			Long idMateria=resultSet.getLong("id_materia");
			
			JDBCAlunnoService jdbcAlunnoService= new JDBCAlunnoService();
			Alunno alunno=jdbcAlunnoService.findAlunnoById(idAlunno);
			
			JDBCRegistroPersonaleService jdbcRegistroPersonaleService=new JDBCRegistroPersonaleService();
			RegistroPersonale registroPersonale=jdbcRegistroPersonaleService.findRegistroPersonaleById(idRegistroPersonale);
			
		    JDBCMateriaService jdbcMateriaService= new JDBCMateriaService();
		    Materia materia=jdbcMateriaService.findMateriaById(idMateria);
			
			voto= new Voto(ide, alunno, valutazione, data, registroPersonale, materia);
		    resultSet.close();
			preparedStatement.close();
			connection.close();
			
			return voto;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		
		return voto;
	}

	@Override
	public List<Voto> findAllVotiByIdAlunno(Long id) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Voto voto=null;
		List<Voto> listVoto = new ArrayList<Voto>();
		String sql=" select * from voti where id = ? ";
		
		try {
        	connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "school", "school");
    		preparedStatement = connection.prepareStatement(sql);
    		preparedStatement.setLong(1, id);
    		preparedStatement.executeQuery();
			resultSet = preparedStatement.getResultSet();
			
			while(resultSet.next())
			{
			Long ide=resultSet.getLong("id");
			Long idAlunno=resultSet.getLong("id_alunno");
			float valutazione=resultSet.getFloat("valutazione");
			Date data= resultSet.getDate("data");
			Long idRegistroPersonale=resultSet.getLong("id_registro_personlae");
			Long idMateria=resultSet.getLong("id_materia");
			
			JDBCAlunnoService jdbcAlunnoService= new JDBCAlunnoService();
			Alunno alunno=jdbcAlunnoService.findAlunnoById(idAlunno);
			
			JDBCRegistroPersonaleService jdbcRegistroPersonaleService=new JDBCRegistroPersonaleService();
			RegistroPersonale registroPersonale=jdbcRegistroPersonaleService.findRegistroPersonaleById(idRegistroPersonale);
			
		    JDBCMateriaService jdbcMateriaService= new JDBCMateriaService();
		    Materia materia=jdbcMateriaService.findMateriaById(idMateria);
			
			voto= new Voto(ide, alunno, valutazione, data, registroPersonale, materia);
		    listVoto.add(voto);
			}
			resultSet.close();
			preparedStatement.close();
			connection.close();
			
			return listVoto;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

}
